package com.example.bjsfpost.services.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.event.SyncReadListener;
import com.example.bjsfpost.domain.*;
import com.example.bjsfpost.mapper.PostParamMapper;
import com.example.bjsfpost.sap.SapCommonResponseDO;
import com.example.bjsfpost.services.PostParamService;
import com.example.bjsfpost.utils.EasyExcelContext;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StopWatch;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.io.File;
import java.io.Serializable;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.concurrent.*;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.ReentrantLock;
import java.util.stream.Collectors;

import static com.example.bjsfpost.controllers.BjsfApiPostController.*;

@Service
@Slf4j
public class PostParamServiceImpl implements PostParamService {
    @Autowired
    private PostParamMapper postParamMapper;
    @Value("${excel.execute.sql-path:}")
    private String sqlPath;
    @Override
    public PageInfo handlerData(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData1(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects1();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData2(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects2();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData3(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects3();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData4(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects4();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData5(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects5();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData6(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects6();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData7(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects7();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData8(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects8();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData9(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects9();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData10(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects10();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData11(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects11();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData12(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects12();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData13(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects13();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData14(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects14();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData15(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects15();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }

    @Override
    public PageInfo handlerData16(Integer pageNum, Integer pageSize) {
        /*
            PageHelper.startPage(pageNum, pageSize);
            List<Student> lists = studentMapper.queryUserInfo();
            PageInfo<Student> pageInfo = new PageInfo<Student>(lists);
            return pageInfo;
        */

        PageHelper.startPage(pageNum, pageSize);
        List<PostParamDO> postParamDOS = postParamMapper.selectObjects16();
        PageInfo<PostParamDO> pageInfo = new PageInfo<>(postParamDOS);
        return pageInfo;
    }
    static ExecutorService WULIAO_HANDLER_THREAD_POOL ;
    static {
        WULIAO_HANDLER_THREAD_POOL = Executors.newFixedThreadPool(16);
    }

    @Override
    public void handlerEtlWuliaoXls() {

 /*       WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData1(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"1");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData2(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"2");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData3(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"3");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData4(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"4");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData5(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"5");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData6(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"6");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData7(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"7");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData8(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"8");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData9(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"9");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData10(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"10");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData11(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"11");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData12(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"12");
        });*/
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData13(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"13");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData14(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"14");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData15(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"15");
        });
        WULIAO_HANDLER_THREAD_POOL.execute(()-> {
            List<WuLiaoExcelData> totalList = new ArrayList<>(16_0000);
            int pageNum = 1;
            int pageSize = 15000;
            Long restTotal = 0L;
            do{
                PageInfo pageInfo = this.handlerData16(pageNum, pageSize);
                List list = pageInfo.getList();
                if(!CollectionUtils.isEmpty(list)){
                    totalList.addAll(list);
                }
                restTotal = restTotal == 0 ? (pageInfo.getTotal() - pageSize) : restTotal - pageSize;
                pageNum++;
            }while (restTotal > 0);
            writeExcel(totalList,"16");
        });
    }

    @Override
    public void handlerAllWuliaoExcelByFileName(String FileDirName,List<String> exclusionFileNames,String url) {
        log.info("===============handlerAllWuliaoExcelByFileName Start ==========================");
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        //读取目标文件
        File fileDir = new File(FileDirName);
        List<ErrorExcelData> result = new ArrayList<>();
        if(fileDir.exists()){
            File[] files = fileDir.listFiles();
            for (File file1 : files) {
                if(file1.isFile() && (file1.getName().contains(".xls") || file1.getName().contains(".xlsx") ) && notINExclusion(exclusionFileNames,file1)){
                    List<ErrorExcelData> oneFileList = handlerOneWuliaoExcelByFileName(file1.getAbsolutePath(),url,fileDir.getAbsolutePath());
                    if(!CollectionUtils.isEmpty(oneFileList)){
                        result.addAll(oneFileList);
                    }else{
                        continue;
                    }
                    boolean b = proccessAto.get();
                    if(!b){
                        while(true){
                            try {
//                                Thread.sleep(120_000);
                                Thread.sleep(10_000);
                            } catch (InterruptedException e) {
                                throw new RuntimeException(e);
                            }
                            b = proccessAto.get();
                            if(b){
                                break;
                            }
                        }
                    }
                }
            }
        }else{
            log.error("handlerAllWuliaoExcelByFileName{} 文件不存在" ,FileDirName);
            throw new RuntimeException("handlerAllWuliaoExcelByFileName  ["+FileDirName+"] 文件不存在");
        }
        //写入ALL excel
        final String currentPath = System.getProperty("user.dir");
//        final String currentPath = fileDir.getAbsolutePath() + File.separator + "result";
        try {
            StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(generatorTime()+ result.size() +"_ALL.xlsx");
            EasyExcelFactory.write(dowloadFilePath.toString()).registerWriteHandler(EasyExcelContext.DEFAULT_EXPORT_STYLE_STRATEGY)
                    .sheet(0).head(ErrorExcelData.class).doWrite(result);
        }catch (Exception e){
            log.info("======handlerAllWuliaoExcelByFileName error=={}",e);
        }finally {
            stopWatch.stop();
            log.info("======handlerAllWuliaoExcelByFileName END size == {}  write excel ",result.size());
        }
/*
        //=====处理Bom4
        successExistsMap.clear();
        existsMap.clear();
        log.info("============================= handler Bom view_4 Start ============");
        //C:\Users\Administrator\Desktop\test-batch\v2\2023-11-24_19-29-32_bom4.xlsx
        String bomPath = "C:\\Users\\Administrator\\Desktop\\test-batch\\v2\\2023-11-24_19-29-32_bom4.xlsx";
        handlerOneWuliaoExcelByFileName(bomPath,"");
        log.info("============================= handler Bom view_4 End ============");
        //=====*/
    }

    private boolean notINExclusion(List<String> exclusionFileNames, File file1) {
        if(!CollectionUtils.isEmpty(exclusionFileNames)){
            for (String exclusionFileName : exclusionFileNames) {
                if(file1.getName().contains(exclusionFileName)){
                    return false;
                }
            }
        }
        return true;
    }

    @Override
    public List<ErrorExcelData> handlerOneWuliaoExcelByFileName(String fileName, String url, String resultAbsPath) {
        log.info("===============handlerOneWuliaoExcelByFileName Start {}==========================",fileName);
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        try{
            //读取历史记录
            final String currentPath = System.getProperty("user.dir");
//            final String currentPath = resultAbsPath;
            final String existPath = currentPath + File.separator + "handler.xlsx";
            final String existPathTemp = currentPath + File.separator + "handler_temp.xlsx";
            File existPathFile = new File(existPath);
            File existPathTempFile = new File(existPathTemp);
            final List<ErrorExcelData> existList = existPathFile.exists() ? EasyExcelFactory.read(existPath, ErrorExcelData.class,
                    new SyncReadListener()).headRowNumber(1).sheet(0).doReadSync() : new ArrayList<>();
            log.info("history records exists size==={}",existList.size());
            List<String> existAndSuccessList = existList.stream().filter(data -> Objects.equals(data.getSuccessFlag(), SUCCESS)).map(ErrorExcelData::getJson).collect(Collectors.toList());
            for (String s : existAndSuccessList) {
                successExistsMap.put(s,true);
            }
            //读目标文件
            List<PostParamDO> readExcelList = EasyExcelFactory.read(fileName, PostParamDO.class,
                    new SyncReadListener()).headRowNumber(1).sheet(0).doReadSync();
            final String Mapping_NAME = CollectionUtils.isEmpty(readExcelList)?"空":readExcelList.get(0).getMatlType();
            log.info("=======Current Handler File Name == {} , Excel size==={}",fileName,readExcelList.size());
            List<ParamPostRequestDO> createPostRequestList = readExcelList.stream().map(data -> {
                ParamPostRequestDO request = new ParamPostRequestDO();
                BeanUtils.copyProperties(data, request);
                return request;
            }).collect(Collectors.toList());
            if(CollectionUtils.isEmpty(createPostRequestList)){
                log.info("======handlerOneWuliaoExcelByFileName {} error size=== 0 {}",fileName);
            }
            //================= 核心 =================
            List<ErrorExcelData> result = handlerOneWuliaoExcel(createPostRequestList,url);
            //写入excel
            try {
                File file = new File(fileName);
                String fileSimpleName = file.getName().substring(0, file.getName().lastIndexOf(".xlsx"));
                StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(fileSimpleName + "_" +Mapping_NAME + "_"+ generatorTime() + ".xlsx");
                EasyExcelFactory.write(dowloadFilePath.toString()).registerWriteHandler(EasyExcelContext.DEFAULT_EXPORT_STYLE_STRATEGY)
                        .sheet(0).head(ErrorExcelData.class).doWrite(result);
            }catch (Exception e){
                log.error("======handlerOneWuliaoExcelByFileName write excel error {}",e);
            }
            //回写历史记录
            if (existPathFile.exists()){
                // 第二次按照原有格式，不需要表头，追加写入
                EasyExcel.write(existPathFile, ErrorExcelData.class).needHead(false).
                        withTemplate(existPathFile).file(existPathTempFile).sheet().doWrite(result);
            }else {
                // 第一次写入需要表头
                EasyExcel.write(existPathFile,ErrorExcelData.class).sheet().doWrite(result);
            }
            if (existPathTempFile.exists()){
                existPathFile.delete();
                existPathTempFile.renameTo(existPathFile);
            }
            return result;
        }catch (Exception e){
            log.info("======handlerOneWuliaoExcelByFileName {} error {}",fileName,e);
            return new ArrayList<>();
        }finally {
            stopWatch.stop();
            log.info("耗时=={}秒",stopWatch.getLastTaskTimeMillis()/1000);
            log.info("===============handlerOneWuliaoExcelByFileName End {}==========================",fileName);
        }
    }

    @Override
    public List<PostParamDO> queryAll4Bom(String FileName) {
        List<PostParamDO> postParamDOS = postParamMapper.select4BomList();
        /*List<WuLiaoExcelData> totalList = postParamDOS.stream().map((data)->{
            WuLiaoExcelData temp = new WuLiaoExcelData();
            BeanUtils.copyProperties(data,temp);
            return temp;
        }).collect(Collectors.toList());
        writeExcel(totalList);*/
        return postParamDOS;
    }

    @Override
    public void handlerBom4() {
        /*List<PostParamDO> postParamDOS = postParamMapper.select4BomList();
        if(!CollectionUtils.isEmpty(postParamDOS)){
            asdf
        }*/
    }

    private List<ErrorExcelData> handlerOneWuliaoExcel(List<ParamPostRequestDO> createPostRequestList,String url) {
        //即将处理的数据
        final int totalSize = createPostRequestList.size();

        if(CollectionUtils.isEmpty(createPostRequestList)){
            throw new RuntimeException("查询数据库为空，请检查配置再试");
        }
        final ReentrantLock reentrantLock = new ReentrantLock();
        final Condition condition = reentrantLock.newCondition();
        //处理请求
        createPostRequestList.forEach(paramPostRequestDO->{
            MSG_HANDLER_THREAD_POOL.submit(()-> handlerThreadM(paramPostRequestDO,url));
//            MSG_HANDLER_THREAD_POOL.submit(()-> handlerThreadMTest(paramPostRequestDO));
        });
        List<ErrorExcelData> resultList = new ArrayList<>(createPostRequestList.size());
        new Thread(()->{
            while(true){
                int size = QUEUE.size();
                System.out.println("QUEUE size == " + size );
                log.info("============== QUEUE size == " + size );
                try {
//                    Thread.sleep(10_000L);
                    Thread.sleep(1_000L);
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
                if(totalSize<=size){
                    try {
                        Thread.sleep(5);
                    } catch (InterruptedException e) {
                        throw new RuntimeException(e);
                    }
                    if(totalSize<=size){
                        reentrantLock.lock();
                        condition.signalAll();
                        reentrantLock.unlock();
                        break;
                    }
                }
            }
        },"listener-queue").start();
        reentrantLock.lock();
        try {
            condition.await();
            resultList.addAll(QUEUE);
            QUEUE.clear();
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }finally {
            reentrantLock.unlock();
            proccessAto.set(true);
        }
        return resultList;
    }

    public static String [] tableName = new String [] {"View_1_ElectricalPart",
            "view_1_StructurePart",
            "view_1_PrintedBoard",
            "view_2_Plug",
            "view_2_StructurePart",
            "view_25_solidifying",
            "view_25_Plug",
            "view_26_VirtualPart",
            "view_3_EndProduct",
            "view_4_Device",
            "view_7_NONproduction",
            "view_7_StandardPart",
            "view_5_standardpinggui",
            "view_5_orderpinggui",
            "view_59_ServerDevice",
            "view_1_OutSourcing"};
    public void writeExcel(List<WuLiaoExcelData> totalList,String mid){
        final String currentPath = System.getProperty("user.dir");
        StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(generatorTime()+"_"+ mid +"_wuliao.xlsx");
//        StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(generatorTime()+"_"+ tableName[Integer.parseInt(mid)-1] +"_wuliao.xlsx");
        log.info("[{}]开始写 =={}，size =={}",tableName[Integer.parseInt(mid)-1],dowloadFilePath,totalList.size());
        EasyExcelFactory.write(dowloadFilePath.toString()).registerWriteHandler(EasyExcelContext.DEFAULT_EXPORT_STYLE_STRATEGY)
                .sheet(0).head(WuLiaoExcelData.class).doWrite(totalList);
    }
    public void writeExcel(List<WuLiaoExcelData> totalList){
        final String currentPath = System.getProperty("user.dir");
        StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(generatorTime()+"_bom4_.xlsx");
//        StringBuffer dowloadFilePath = new StringBuffer(currentPath).append(File.separator).append(generatorTime()+"_"+ tableName[Integer.parseInt(mid)-1] +"_wuliao.xlsx");
        EasyExcelFactory.write(dowloadFilePath.toString()).registerWriteHandler(EasyExcelContext.DEFAULT_EXPORT_STYLE_STRATEGY)
                .sheet(0).head(WuLiaoExcelData.class).doWrite(totalList);
    }
    private String generatorTime() {
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        return sf.format(new Date());
    }

    @Autowired
    private DataSource dataSource;
    @Override
    public SapCommonResponseDO executeSql2Excel() {
        if(StringUtils.hasText(sqlPath)){
            File sqlPathFile = new File(sqlPath);
            final List<SQLExcel> sqlExcelList = sqlPathFile.exists() ? EasyExcelFactory.read(sqlPath, SQLExcel.class,
                    new SyncReadListener()).headRowNumber(1).sheet(0).doReadSync() : new ArrayList<>();
            log.info("sqlExcelList size==={}",sqlExcelList.size());
            if(CollectionUtils.isEmpty(sqlExcelList)){
                return SapCommonResponseDO.fail(sqlPath + "文件不存在或者excel文件为空！");
            }
            try {
                Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                for ( int i =0 ; i < sqlExcelList.size() ;i++) {
                    SQLExcel sqlExcel = sqlExcelList.get(i);
                    List<WuLiaoExcelData> localList = new ArrayList<>();
                    try{
                        ResultSet rs = statement.executeQuery(sqlExcel.getSql());
                        //获取查询结果
                        ResultSetMetaData rm = rs.getMetaData();
                        int columnCount = rm.getColumnCount();
                        //封装结果集
                        //SELECT oid as itemId ,url as version,method as matlType FROM ty_api_record limit 10;
                        HashMap<String, Object> map = new HashMap<>(columnCount);
                        for (int j = 1; j <= columnCount; j++) {
                            String name = rm.getColumnName(j).toLowerCase();
                            Object value = rs.getObject(j);
                            map.put(name,value);
                        }
                        WuLiaoExcelData data = new WuLiaoExcelData();
                        data.setVersion(map.getOrDefault("version","").toString());
                        data.setItemId(map.getOrDefault("itemid","").toString());
                        data.setMatlType(map.getOrDefault("matltype","").toString());
                        localList.add(data);
                    }catch (Exception e){
                        log.error("executeSql2Excel {}==error",sqlExcel,e);
                    }
                    //写excel
                    writeExcel(localList,String.valueOf(i));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return SapCommonResponseDO.ok("导出完成");
        }else{
            return SapCommonResponseDO.fail("excel.execute.sql-path 参数未配置");
        }
    }
    @Data
    public static class SQLExcel implements Serializable {
        @ExcelProperty(value = "SQL" , order = 1)
        private String sql;
    }
}
